Updated SQL Server 2000 Sample Database Scripts

February 15, 2004

© Copyright Microsoft Corporation, 2004. All rights reserved.

 
The SQL Server documentation team cannot answer technical support questions, but welcomes your suggestions and comments about this readme documentation. You can quickly and directly send e-mail feedback using the link below. Please send feedback in English.

To submit written feedback about this document, click here:  Submit feedback.
 

The Microsoft® SQL Server™ 2000 Sample Database Scripts include new versions of two scripts included with SQL Server 2000:

• Instpubs.sql creates the pubs sample database.
• Instnwnd.sql creates the Northwind sample database.

These copies of the scripts are the same as the versions included with SQL Server 2000, except for these changes:

• The script for creating pubs, instpubs.sql, has been modified to install pubs and all of the pubs data in one step. The version of instpubs.sql included in SQL Server 2000 does not load in some of the pubs data, customers had to run batch jobs to install some of the larger data values.
• The scripts improve security by not creating a guest account and not assigning permissions to the objects in the sample databases. Customers who install the sample databases can now control access to the sample databases.

SQL Server 2000 Desktop Engine (MSDE 2000) customers can also use these copies of the scripts to install pubs and Northwind.

Sample Script Prerequisites

The user running the SQL Server 2000 Sample Database Scripts must have CREATE DATABASE permissions.

The scripts can be used with the following versions of SQL Server 2000:

• SQL Server 2000 Service Pack 3 (SP3) or later.
• SQL Server 2000 Desktop Engine (MSDE 2000) SP3 or later.
• SQL Server 2000 Desktop Engine (MSDE 2000) Release A.

Downloading and Extracting the Scripts

The SQL Server 2000 Sample Database Scripts are available as an installation file, SQL2000SampleDb.msi, which can be downloaded by following the instructions on this Web site.

After downloading SQL2000SampleDb.msi, extract the sample database scripts by double-clicking SQL2000SampleDb.msi. SQL2000SampleDb.msi will extract the database scripts and this readme file into this default folder:

C:\Program Files\SQL Server 2000 Sample Databases

Installing the Sample Databases

To install the sample databases, you:

1. Run either the Instpubs.sql file or the Instnwnd.sql file to create the database and load in the data.
2. If you want to grant another person access to the sample database, you must grant permissions on the database objects to that person.

SQL Server 2000 customers can perform these tasks using either the osql utility or the graphical utilities SQL Query Analyzer and SQL Enterprise Manager. MSDE 2000 only includes the osql command prompt utility.

Caution  Instpubs.sql drops any existing version of the pubs sample database and Instnwnd.sql drops any existing copy of the Northwind sample database before creating new copies of the databases.

Running the Scripts Using SQL Query Analyzer

To run the scripts using SQL Query Analyzer:

1. Select the Query Analyzer icon in the Microsoft SQL Server program group in the Start menu.
2. Connect to the instance of SQL Server 2000 on which you want to create the sample database.
3. Open the File/Open menu and navigate to the c:\program files\SQL Server 2000 Sample Databases folder. Select either the Instpubs.sql or Instnwnd.sql file.
4. Click the Execute Query button on the toolbar.

Running the Scripts Using osql

To run the scripts using osql:

1. Open a Command Prompt window.
2. From the command prompt, use the cd command to navigate to the folder containing the SQL Server 2000 Sample Database Scripts:

   cd c:\program files\SQL Server 2000 Sample Databases

3. Run osql specifying either Instpubs.sql or Instnwnd.sql as the input file. These are some examples:
   • To install the Northwind database on a default instance on the same computer:

     osql -E -i instnwnd.sql

   • To install the pubs database on a default instance on a different computer:

     osql -E -S ComputerName -i instpubs.sql

   • To install the Northwind database on a named instance:

     osql -E -S ComputerName/InstanceName -i instnwnd.sql

Assigning Permissions

The login you use when running a SQL Server 2000 Sample Database Script becomes the owner of the sample database created by the script, and all of the objects in the sample database. That login and any login in the sysadmin fixed server role have permission to access the data and objects in the sample database. All other logins must be given permissions before they can access the sample data and objects.

Granting a Login Access to a Database

A member of the sysadmin fixed server role or the db_accessadmin and db_owner fixed database roles must:

• Grant each login permissions to access the database.
• Associate the login with a user account in the database.

You can use Enterprise Manager to grant access to the database:

1. Expand the server group and then expand the server.
2. Expand Databases and select the sample database.
3. Right click Users, and then click New Database User.

In either osql or SQL Query Analyzer you can use the sp_grantdbaccess system stored procedure to grant access to the sample database:

USE pubs;
GO
sp_grantdbaccess N'login_name', N'database_user_account';
GO

For more information about granting logins access to a database, see the following topics in the SQL Server Books Online:

Granting a Windows User or Group Access to a Database

Granting a SQL Server Login Access to a Database

Granting User Accounts Permissions

Once a login has been associated with a user account in the sample database, a member of the sysadmin fixed server role or the db_accessadmin and db_owner fixed database roles must grant that user account permissions to access database objects.

You can use Enterprise Manager to grant access to the database:

1. Expand the server group and then expand the server.
2. Expand Databases and select the sample database.
3. Expand the category for type of object, such as stored procedures or views.
4. Right click the object, point to All Tasks, and then click Manage Permissions.

In either osql or SQL Query Analyzer you can use the GRANT statement to grant permissions on database objects. First execute a USE <database_name> statement. To grant execute permission on a stored procedure, use:

GRANT EXECUTE ON [dbo].[database_object_name]
              TO [database_user_account];
GO

To grant data read and modification permissions, use:

GRANT  SELECT , INSERT , DELETE , UPDATE
       ON [dbo].[database_object_name]
       TO [database_user_account];
GO

For more information about granting permissions in a database, see the following topic in the SQL Server Books Online:

Granting Permissions